package com.han.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import org.apache.log4j.Logger;
import com.han.bom.Answer;
import com.han.bom.Exam;
import com.han.bom.ExamResponse;
import com.han.bom.MultipleChoice;
import com.han.bom.PracticeExam;
import com.han.bom.Question;
import com.han.bom.RealExam;
import com.han.bom.Student;
import com.han.bom.TrueOrFalse;

public class ExamResponseDAO {
	private Connection connection;
	private PreparedStatement preparedStatement;
	private ResultSet resultSet;
	private static Logger logger = Logger.getLogger(ExamResponseDAO.class);
	
	public void connect() throws ClassNotFoundException, SQLException{
		try{
			Class.forName("com.mysql.jdbc.Driver");
			this.connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/beta_test_engine", "root", "");
		}catch(ClassNotFoundException e){
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		}
	}
	
	public void disconnect() throws SQLException{
		if(this.resultSet!=null){
			try{
				this.resultSet.close();
			}catch(SQLException e){
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
		
		if(this.preparedStatement!=null){
			try {
				this.preparedStatement.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
		
		if(this.connection!=null){
			try {
				this.connection.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
	}
	
	public ExamResponse retrieveResponseById(String erId) throws SQLException, ClassNotFoundException{
		ExamResponse response = null;
		Date date = null;
		int index1 = erId.indexOf("S");
		int index2 = erId.indexOf("T");
		String studentId = erId.substring(index1, index2);
		Question question = null;
		String answerid = "";
		int index = 0;
		double grade = 0;
		
		try {
			this.connect();

			String select = "SELECT c.date_taken,a.answer,a.answer_id FROM tbl_choice AS a INNER JOIN tbl_questions_answered AS b ON a.answer_id = b.answer_id RIGHT OUTER JOIN tbl_exam_response AS c ON b.exam_response_id=c.exam_response_id WHERE c.exam_response_id=?";

			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setString(1, erId);
			this.resultSet = this.preparedStatement.executeQuery();
			
			response = new ExamResponse(new RealExam(), studentId, new ArrayList<Answer>());
			response.setExamResponseID(erId);
			while(resultSet.next()){
				date = new Date(this.resultSet.getTimestamp("date_taken").getTime());
				grade = this.resultSet.getDouble("exam_grade");
					
				if(this.resultSet.getString("answer") != null)
				{
					answerid = this.resultSet.getString("answer_id");
					index = answerid.indexOf("F");
						
					if(index != -1){
						question = new TrueOrFalse();
						question.setQuestionID(answerid.substring(0, index+1));
					}else{
						index = answerid.indexOf("C");
						question = new MultipleChoice();
						question.setQuestionID(answerid.substring(0, index+1));
					}
						
					String answer = this.resultSet.getString("answer");
					Answer ans = new Answer(question, answer);
					response.getAnswers().add(ans);
				}
			}
			response.setDateTaken(date);
			response.setGrade(grade);
		} catch (ClassNotFoundException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.disconnect();
		}
		
		return response;
	}
	
	public ArrayList<ExamResponse> retrieveResponsesByStudentAndExam(String studentId, Exam exam) throws SQLException, ClassNotFoundException{
		ArrayList<ExamResponse> responses = new ArrayList<ExamResponse>();
		ExamResponse response = null;
		Question question = null;
		int count=(exam instanceof RealExam)?((RealExam) exam).getTimesActivated():((PracticeExam) exam).getTimesTaken();
		String examrespid = "";
		
		try {
			this.connect();
			
			for(int i=1;i<=count;i++){
				examrespid = "T"+i;
				String select = "SELECT * FROM tbl_choice AS a INNER JOIN tbl_questions_answered AS b ON a.answer_id = b.answer_id RIGHT OUTER JOIN tbl_exam_response AS c ON b.exam_response_id=c.exam_response_id WHERE c.exam_response_id=?";
				this.preparedStatement = this.connection.prepareStatement(select);
				this.preparedStatement.setString(1, exam.getExamID()+studentId+examrespid);
				this.resultSet = this.preparedStatement.executeQuery();
				
				if(resultSet.isBeforeFirst()){
					response = new ExamResponse(exam, studentId, new ArrayList<Answer>());
					response.setExamResponseID(exam.getExamID()+studentId+examrespid);
					while(resultSet.next()){
						if(this.resultSet.getString("answer") != null)
						{
							int questionid = this.resultSet.getInt("question_id");
								
							question = exam.searchQuestion("Q"+questionid+"MC");
								
							if(question == null){
								question = exam.searchQuestion("Q"+questionid+"TF");
							}
								
							String answer = this.resultSet.getString("answer");
							Answer ans = new Answer(question, answer);
							response.getAnswers().add(ans);
						}
						response.setDateTaken(new Date(this.resultSet.getTimestamp("date_taken").getTime()));
						response.setGrade(this.resultSet.getDouble("exam_grade"));
					}
					responses.add(response);
				}
			}
		} catch (ClassNotFoundException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.disconnect();
		}
		return responses;
	}
	
	public ArrayList<String> retrieveExamsTaken(String studentId) throws SQLException, ClassNotFoundException{
		ArrayList<String> examstaken = new ArrayList<String>();
		String examtaken = "";
		int id = 0;
		boolean type = false;
		
		try{
			id = Integer.parseInt(studentId.substring(1));
		}catch(NumberFormatException e){
			id = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT DISTINCT e.exam_id, e.exam_type FROM tbl_exam AS e INNER JOIN tbl_exam_response AS b ON e.exam_id = b.exam_id WHERE b.student_id = ? AND b.istaken = true";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.resultSet = this.preparedStatement.executeQuery();
				
			while(this.resultSet.next()){
				type = this.resultSet.getBoolean(2);
					
				if(type){
					examtaken = "E"+this.resultSet.getInt(1)+"RE";
				}else{
					examtaken = "E"+this.resultSet.getInt(1)+"PE";
				}
				examstaken.add(examtaken);
			}
		} catch (ClassNotFoundException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.disconnect();
		}
		
		return examstaken;
	}
	
	public ArrayList<ExamResponse> retrieveExamsMissed(String studentId) throws SQLException, ClassNotFoundException{
		ArrayList<ExamResponse> examstaken = new ArrayList<ExamResponse>();
		RealExam examtaken = null;
		ExamResponse missed = null;
		int id = 0;
		Date date = null;
		
		try{
			id = Integer.parseInt(studentId.substring(1));
		}catch(NumberFormatException e){
			id = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT a.exam_id,a.exam_name,a.exam_duration,a.exam_passing,b.date_taken FROM tbl_exam AS a INNER JOIN tbl_exam_response AS b ON a.exam_id=b.exam_id WHERE b.student_id=? AND b.istaken=false";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.resultSet = this.preparedStatement.executeQuery();
				
			while(this.resultSet.next()){
				examtaken = new RealExam();
				examtaken.setExamID("E"+this.resultSet.getInt(1)+"RE");
				examtaken.setExamName(this.resultSet.getString(2));
				examtaken.setDuration(this.resultSet.getLong(3));
				examtaken.setPassingPercent(this.resultSet.getDouble(4));
				date = new Date(this.resultSet.getTimestamp(5).getTime());
				missed = new ExamResponse(examtaken,studentId,new ArrayList<Answer>());
				missed.setDateTaken(date);
				examstaken.add(missed);
			}
		} catch (ClassNotFoundException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.disconnect();
		}
		
		return examstaken;
	}
	
	public int retrieveResponseCountByStudentAndExam(String studentId, String examId) throws SQLException, ClassNotFoundException{
		int studentid = Integer.parseInt(studentId.substring(1));
		int examid = 0;
		int count = 0;
		
		try{
			examid = Integer.parseInt(examId.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			examid = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT COUNT(*) FROM tbl_exam_response WHERE student_id=? AND exam_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, studentid);
			this.preparedStatement.setInt(2, examid);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				count = this.resultSet.getInt(1);
			}
		} catch (ClassNotFoundException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.disconnect();
		}
		return count;
	}
	
	public boolean insertExamResponse(Student student, boolean taken) throws ClassNotFoundException, SQLException{
		boolean success = false;
		String insert = "INSERT IGNORE INTO tbl_exam_response(exam_response_id,student_id,exam_id,date_taken,istaken,exam_grade) VALUES(?,?,?,?,?,?)";
		Date now = new Date();
		int eid = 0;
		
		try{
			eid = Integer.parseInt(student.getExamAnswers().get(0).getExam().getExamID().replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			eid = 0;
		}
		
		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);
			
			int id = Integer.parseInt(student.getUserID().substring(1));
			this.preparedStatement.setString(1, student.getExamAnswers().get(0).getExamResponseID());
			this.preparedStatement.setInt(2, id);
			this.preparedStatement.setInt(3, eid);
			this.preparedStatement.setTimestamp(4, new Timestamp(now.getTime()));
			this.preparedStatement.setBoolean(5, taken);
			this.preparedStatement.setDouble(6, 0);
			int row = this.preparedStatement.executeUpdate();
			success=(row>0)?true:false;
		} catch (SQLException e) {
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean updateExamResponseGrade(ExamResponse response) throws ClassNotFoundException, SQLException{	
		boolean success = false;
		String update = "UPDATE tbl_exam_response SET exam_grade=? WHERE exam_response_id=?";
		int row = 0;
		
		try {
			this.connect();
			this.preparedStatement = this.connection.prepareStatement(update);
			
			this.preparedStatement.setDouble(1, response.getGrade());
			this.preparedStatement.setString(2, response.getExamResponseID());
			
			success=(row>0)?true:false;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean updateExamResponses(ExamResponse response) throws ClassNotFoundException, SQLException{	
		boolean success = false;
		String update = "UPDATE tbl_questions_answered SET answer_id=(SELECT answer_id FROM tbl_choice WHERE question_id=? AND answer=?) WHERE exam_response_id=? AND question_id=?";
		int row = 0;
		
		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(update);
			
			for(Answer answer: response.getAnswers()){
				int questionid = Integer.parseInt(answer.getQuestion().getQuestionID().replaceAll("[^\\d]", ""));
				
				this.preparedStatement.setInt(1, questionid);
				this.preparedStatement.setString(2, answer.getAnswer());
				this.preparedStatement.setString(3, response.getExamResponseID());
				this.preparedStatement.setInt(4, questionid);
				row += this.preparedStatement.executeUpdate();
			}
			
			this.connection.commit();
			
			success=(row>0)?true:false;
		} catch (SQLException e) {
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean deleteExamResponses(String examresponseid) throws ClassNotFoundException, SQLException{
		boolean success = false;
		String delete = "DELETE FROM tbl_questions_answered WHERE exam_response_id=?";
		
		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(delete);
			
			this.preparedStatement.setString(1, examresponseid);
			int row = this.preparedStatement.executeUpdate();
			
			delete = "DELETE FROM tbl_exam_response WHERE exam_response_id=?";
			
			this.preparedStatement = this.connection.prepareStatement(delete);
			
			this.preparedStatement.setString(1, examresponseid);
			int row2 = this.preparedStatement.executeUpdate();
			this.connection.commit();
			
			success=(row>0&&row2>0)?true:false;
		} catch (SQLException e) {
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean submitResponses(ExamResponse response) throws SQLException{
		boolean success = false;
		String insert = "INSERT INTO tbl_questions_answered(exam_response_id,question_id,answer_id) VALUES(?,?,(SELECT answer_id FROM tbl_choice WHERE question_id = ? AND answer=?))";
		int row2 = 0;
		int size = 0;
		
		try{
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);
			
			for(Answer answer: response.getAnswers()){
				if(!answer.getAnswer().equals("")){
					int questionid = Integer.parseInt(answer.getQuestion().getQuestionID().replaceAll("[^\\d]", ""));
					this.preparedStatement.setString(1, response.getExamResponseID());
					this.preparedStatement.setInt(2, questionid);
					this.preparedStatement.setInt(3, questionid);
					this.preparedStatement.setString(4, answer.getAnswer());
					size++;
					row2 += this.preparedStatement.executeUpdate();
				}
			}
			this.connection.commit();
			success = (row2==size)?true:false;
		}catch(SQLException e){
			this.connection.rollback();
			logger.error(e.getMessage(), e);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	public PreparedStatement getPreparedStatement() {
		return preparedStatement;
	}

	public void setPreparedStatement(PreparedStatement preparedStatement) {
		this.preparedStatement = preparedStatement;
	}

	public ResultSet getResultSet() {
		return resultSet;
	}

	public void setResultSet(ResultSet resultSet) {
		this.resultSet = resultSet;
	}
}
